package cn.kgc.jdbc.demo;

import cn.kgc.jdbc.entity.User;
import cn.kgc.jdbc.utils.DruidUtil;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 * @author: mosin
 * @version: v1.0  2022/4/20
 *
 *  测试数据库连接池的使用
 *
 */
public class TestDruid {

    public static void main(String[] args) {
            test06();
    }

    //使用数据库连接池 进行数据的查询
    public static void test01(){
        //获取连接
        Connection connection = DruidUtil.getConnection();
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        String sql = "select *  from usr";
        try {
             preparedStatement = connection.prepareStatement(sql);
             resultSet = preparedStatement.executeQuery();
            ArrayList<User> users = new ArrayList<>();
            while (resultSet.next()){
                int id = resultSet.getInt("id");
                String uname = resultSet.getString("uname");
                String pwd = resultSet.getString("pwd");

                //User user = new User();
                //user.setId(id);
                //user.setPassword(pwd);
                //user.setUname(uname);

                //User user = User.builder().id(id).password(pwd).uname(uname).build();
                User user = new User(id, uname, pwd);
                users.add(user);
                //System.out.println("user = " + user);
            }
            users.forEach(user -> System.out.println("user = " + user));

        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            DruidUtil.closeAll(resultSet, preparedStatement, connection);
        }

    }

    //DBUtils工具类的使用
    public static void test02(){
        //创建queryRunner  操作数据库的对象
        QueryRunner queryRunner = new QueryRunner(DruidUtil.getDataSource());
        //查询数据库中的所有数据  usr
        String sql = "select *  from usr"; // list集合  泛型
        try {
            // 为了保证数据封装的准确性 要让数据库中查询的字段名称和实体类中属性的名字保持一致  否则封装失败   反射
            List<User> users = queryRunner.query(sql, new BeanListHandler<>(User.class));
            //遍历结果集
            users.forEach(user -> System.out.println("user = " + user));
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }

    }


    public static void test03(){ // 有条件查询

        QueryRunner queryRunner = new QueryRunner(DruidUtil.getDataSource());
        String sql =" select *  from  usr  where id = ?";
        try {
            User user = queryRunner.query(sql, new BeanHandler<>(User.class), 12);
            System.out.println("user = " + user);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
    }


    public static void test04(){ // 添加数据

        QueryRunner queryRunner = new QueryRunner(DruidUtil.getDataSource());
        String sql =" insert into usr (uname,pwd)values(?,?)";

        try {
            int line = queryRunner.update(sql, "dd", "123456");
            System.out.println("line = " + line);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }

    }

    public static void test05() { // 更新数据

        QueryRunner queryRunner = new QueryRunner(DruidUtil.getDataSource());
        String sql =" update usr set pwd = ? where id = ?";
        try {
            int line = queryRunner.update(sql, "111111", 19);
            System.out.println("line = " + line);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }

    }


    public static void test06() { // 删除数据
        QueryRunner queryRunner = new QueryRunner(DruidUtil.getDataSource());
        String sql ="delete from usr where id = ?";
        try {
            int line = queryRunner.update(sql, 19);
            System.out.println("line = " + line);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }

    }

}
